import sqlite3
import pandas as pd
from module.log import *
import os


def get_sqlite_names(db_path):
    """
    查询当前数据库下的所有表名
    :param db_path:
    :return: list:[table names]
    """
    try:

        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()

        cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")

        table_names = cursor.fetchall()

        cursor.close()
        conn.close()

        return [table[0] for table in table_names]

    except Exception as e:
        log_exception(e)
        raise


def search_sql_to_df(db_path, table_name, select_condition='*'):
    """
    :param db_path:
    :param table_name:
    :param select_condition:
    :return: dataframe
    """
    try:
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()

        cursor.execute(f"SELECT {select_condition} FROM {table_name};")
        data = cursor.fetchall()

        cursor.execute(f"PRAGMA table_info({table_name});")
        columns = cursor.fetchall()
        column_names = [column[1] for column in columns]

        df = pd.DataFrame(data, columns=column_names)

        cursor.close()
        conn.close()

        return df

    except Exception as e:
        log_exception(e)
        raise

def txt_extract(resources, nums, txt_path):
    with open(txt_path, 'w', encoding='utf-8') as file:
        for i in range(len(resources)):
            file.write(str(resources[i]) + ':' + str(nums[i]) + '\n')

def delete_record(db_path, table_name, pri_key, key_content):
    """
    根据主键值删除表中记录
    :param db_path: 数据库文件路径
    :param table_name: 表名
    :param pri_key: 主键字段名
    :param key_content: 要删除的主键值
    """
    try:
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()

        # 使用参数化防止 SQL 注入
        sql = f"DELETE FROM {table_name} WHERE {pri_key} = ?;"
        cursor.execute(sql, (key_content,))

        conn.commit()
        print(f"已删除 {table_name} 表中 {pri_key} = {key_content} 的记录。")

        cursor.close()
        conn.close()
    except Exception as e:
        log_exception(e)
        raise

def update_record(db_path, table_name, pri_key, key_content, update_key, update_content):
    """
    根据主键更新指定记录的字段值
    :param db_path: 数据库路径
    :param table_name: 表名
    :param pri_key: 主键字段名
    :param key_content: 主键对应的值
    :param update_key: 要更新的字段名列表
    :param update_content: 对应字段的值列表
    """
    try:
        if not update_key or not update_content or len(update_key) != len(update_content):
            raise ValueError("字段名和内容列表必须长度一致且非空")

        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()

        # 构造 SET 子句
        set_clause = ", ".join([f"{k} = ?" for k in update_key])
        sql = f"UPDATE {table_name} SET {set_clause} WHERE {pri_key} = ?"

        # 参数列表
        params = update_content + [key_content]

        cursor.execute(sql, params)
        conn.commit()

        print(f"已更新 {table_name} 表中 {pri_key} = {key_content} 的记录。")

        cursor.close()
        conn.close()

    except Exception as e:
        log_exception(e)
        raise

if __name__ == '__main__':
    db_path = os.path.join(os.getcwd(), "test.db")
    table_name = 'sync'
    #df = search_sql_to_df(db_path, table_name)
    #print(df)
    #update_record(db_path, "sync", "ID", "000002", ["name"], ["update"])
    delete_record(db_path, "sync", "ID", "000002")